package lab3;
import java.lang.reflect.*;
import java.sql.*;
import java.util.List;
public class SqlUtil {
	private static final String driver="com.mysql.jdbc.Driver";
	private static final String url="jdbc:mysql://localhost:3306/employees?useSSL=false";
	private static final String username="root";
	private static final String password="zglq8822";
	private Connection connection;
	private PreparedStatement preparedStatement;
	public void dbManager(String statement) {
		try {
			Class.forName(driver);
			connection=DriverManager.getConnection(url,username,password);
			preparedStatement=connection.prepareStatement(statement);
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
	String query(User user) {
		String statement="SELECT * FROM ";
		try {
			Class clz=user.getClass();
			Table tx=(Table)clz.getAnnotation(Table.class);
			statement+=tx.value()+" WHERE ";
			Field[] f=clz.getDeclaredFields();
			for(Field item:f) {
				item.setAccessible(true);
				Column co=item.getAnnotation(Column.class);
				if(co.value().equals("id")&&(int)item.get(user)!=0) {
					statement+="id="+(int)item.get(user)+" ";
				}
				if(co.value().equals("username")&&!item.get(user).equals("")) {
					statement+="username LIKE"+" '"+item.get(user)+"' ";
				}
				if(co.value().equals("age")&&(int)item.get(user)!=0) {
					statement+="age="+(int)item.get(user)+" ";
				}
				if(co.value().equals("email")&&!item.get(user).equals("")) {
					statement+="email LIKE "+"'"+item.get(user)+"' ";
				}
				if(co.value().equals("telephone")&&!item.get(user).equals("")) {
					statement+="telephone LIKE "+"'"+item.get(user)+"'";
				}
			}
			statement+=";";
		}catch(Exception e) {
			e.printStackTrace();
		}
		dbManager(statement);
		try {
			ResultSet resultSet = preparedStatement.executeQuery(statement);
			while(resultSet.next()) {
				int id=resultSet.getInt(1);
				String username=resultSet.getString(2);
				int age=resultSet.getInt(3);
				String email=resultSet.getString(4);
				String telephone=resultSet.getString(5);
				System.out.println(id+" "+username+" "+age+" "+email+" "+telephone);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return statement;
	}
	String insert(User user) {
		String statement="INSERT INTO ";
		try {
			Class clz=user.getClass();
			Table tx=(Table)clz.getAnnotation(Table.class);
			statement+=tx.value()+" (";
			Field[] f=clz.getDeclaredFields();
			for(int i=0;i<f.length;i++) {
				f[i].setAccessible(true);
				if(f[i].getAnnotation(Column.class).value().equals("id")||f[i].getAnnotation(Column.class).value().equals("age")) {
					if((int)f[i].get(user)==0){
						continue;
					}
				}else {
					if(f[i].get(user).equals("")) {
						continue;
					}
				}
				statement+=f[i].getAnnotation(Column.class).value();
				if(i!=f.length-1) {
					statement+=",";
				}else {
					statement+=") VALUES (";
				}
			}
			for(int i=0;i<f.length;i++) {
				f[i].setAccessible(true);
				if(f[i].getAnnotation(Column.class).value().equals("id")||f[i].getAnnotation(Column.class).value().equals("age")) {
					if((int)f[i].get(user)==0){
						continue;
					}else {
						statement+=f[i].get(user);
					}
				}else {
					if(f[i].get(user).equals("")) {
						continue;
					}else {
						statement+="'"+f[i].get(user)+"'";
					}
				}
				
				if(i!=f.length-1) {
					statement+=",";
				}else {
					statement+=")";
				}
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
		statement+=";";
		dbManager(statement);
		try {
			int resultSet = preparedStatement.executeUpdate(statement);
			System.out.println(resultSet);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return statement;
	}
	String insert(List<User> users) {
		String statement="INSERT INTO ";
		try {
			Class clz=users.get(0).getClass();
			Table tx=(Table)clz.getAnnotation(Table.class);
			statement+=tx.value()+" (";
			Field[] f=clz.getDeclaredFields();
			for(int i=0;i<f.length;i++) {
				f[i].setAccessible(true);
				if(f[i].getAnnotation(Column.class).value().equals("id")||f[i].getAnnotation(Column.class).value().equals("age")) {
					if((int)f[i].get(users.get(0))==0){
						continue;
					}
				}else {
					if(f[i].get(users.get(0)).equals("")) {
						continue;
					}
				}
				statement+=f[i].getAnnotation(Column.class).value();
				if(i!=f.length-1) {
					statement+=",";
				}else {
					statement+=") VALUES (";
				}
			}
			for(int i=0;i<users.size();i++) {
				Class clz1=users.get(i).getClass();
				Table tx1=(Table)clz1.getAnnotation(Table.class);
				Field[] f1=clz.getDeclaredFields();
				for(int j=0;j<f1.length;j++) {
					f1[j].setAccessible(true);
					if(f[j].getAnnotation(Column.class).value().equals("id")||f1[j].getAnnotation(Column.class).value().equals("age")) {
						if((int)f1[j].get(users.get(i))==0){
							continue;
						}else {
							statement+=f1[j].get(users.get(i));
						}
					}else {
						if(f1[j].get(users.get(i)).equals("")) {
							continue;
						}else {
							statement+="'"+f1[j].get(users.get(i))+"'";
						}
					}
					
					if(j!=f1.length-1) {
						statement+=",";
					}else if(i!=users.size()-1){
						statement+="),(";
					}else {
						statement+=")";
					}
				}
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
		statement+=";";
		dbManager(statement);
		try {
			int resultSet = preparedStatement.executeUpdate(statement);
			System.out.println(resultSet);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return statement;
	}
	String delete(User user) {
		String statement="DELETE FROM ";
		try {
			Class clz=user.getClass();
			Table tx=(Table)clz.getAnnotation(Table.class);
			statement+=tx.value()+" WHERE ";
			Field[] f=clz.getDeclaredFields();
			for(Field item:f) {
				item.setAccessible(true);
				Column co=item.getAnnotation(Column.class);
				if(co.value().equals("id")&&(int)item.get(user)!=0) {
					statement+="id="+(int)item.get(user)+" ";
				}
				if(co.value().equals("username")&&!item.get(user).equals("")) {
					statement+="username LIKE"+" '"+item.get(user)+"' ";
				}
				if(co.value().equals("age")&&(int)item.get(user)!=0) {
					statement+="age="+(int)item.get(user)+" ";
				}
				if(co.value().equals("email")&&!item.get(user).equals("")) {
					statement+="email LIKE "+"'"+item.get(user)+"' ";
				}
				if(co.value().equals("telephone")&&!item.get(user).equals("")) {
					statement+="telephone LIKE "+"'"+item.get(user)+"'";
				}
			}
			statement+=";";
		}catch(Exception e) {
			e.printStackTrace();
		}
		dbManager(statement);
		try {
			int resultSet = preparedStatement.executeUpdate(statement);
			System.out.println(resultSet);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return statement;
	}
	String update(User user) {
		String statement="UPDATE ";
		Class clz=user.getClass();
		Table tx=(Table)clz.getAnnotation(Table.class);
		statement+=tx.value()+" SET ";
		Field[] f=clz.getDeclaredFields();
		try {
			for(int i=1;i<f.length;i++) {
				f[i].setAccessible(true);
				if(f[i].getAnnotation(Column.class).value().equals("age")) {
					if((int)f[i].get(user)==0){
						continue;
					}
				}else {
					if(f[i].get(user).equals("")) {
						continue;
					}else {
						statement+=f[i].getAnnotation(Column.class).value()+"="+"'"+f[i].get(user)+"'"+" ";
					}
				}
			}
			f[0].setAccessible(true);
			statement+="WHERE "+f[0].getAnnotation(Column.class).value()+"="+f[0].get(user);
		}catch(Exception e) {
			e.printStackTrace();
		}
		statement+=";";
		dbManager(statement);
		try {
			int resultSet = preparedStatement.executeUpdate(statement);
			System.out.println(resultSet);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return statement;
	}
}
